﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IrisGenerator.Common.Modules;
using IrisGenerator.Common.DatabaseModel;
using System.IO;
using IrisGenerator.Modules.SqlServer2005.Formater;

namespace IrisGenerator.Modules.SqlServer2005.Generators
{
    [ModuleName("SqlServer 2005 Stored Procedure Writer")]
    [ModuleDescription("This module writes Stored Procedures for SQL Server 2005.")]
    [ModuleVersion(0, 1)]
    [ModuleTarget("SQL2005", "Sql Server 2005", "A module that targets Sql Server 2005 databases.")]
    public class SpGenerator:ModuleBase
    {

        [ModuleParameter("Database", "IrisGenerator.Common.DatabaseModel.Database", false, ModuleParameterDirection.InOut)]
        public Database Database { get; set; }

        public string OutputPath { get;private set; }

        private StreamWriter OutputFile=null;
        private SqlServerFormatter Formatter;


        public override bool Analyse()
        {

            this.OutputPath = @"c:\Generation\SQL";
            SetDatabase();
            this.Formatter = new SqlServerFormatter();
            

            try
            {
                OutputFile = new StreamWriter(Path.Combine(this.OutputPath , "SqlServer2005.StoredProcedures.sql"));
                OutputFile.AutoFlush = true;
                foreach (Table table in this.Database.TableCollection)
                {
                    //TODO: Change implementing request types... Change the database model.
                    foreach (Request request in table.RequestCollection)
                    {
                        if (request.SelectColumnsCollection.Count > 0)
                        {
                            CreateSelectProcedure(request);
                        }
                        else if (request.UpdateColumnsCollection.Count > 0)
                        {
                            CreateUpdateProcedure(request);
                        }
                        else if (request.InsertColumnsCollection.Count > 0)
                        {
                            CreateInsertProcedure(request);
                        }
                        else
                        {
                            CreateDeleteProcedure(request);
                        }
                    }
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (this.OutputFile != null)
                {
                    this.OutputFile.Flush();
                    this.OutputFile.Close();
                    this.OutputFile.Dispose();
                }
            }

            return true;
        }

        private void CreateDeleteProcedure(Request request)
        {
            DeleteExistingRequest(request);

            string code = CreateProcedureHeader(request) + Environment.NewLine;
            code += CreateParametersDeclaration(request) + Environment.NewLine;
            code += "BEGIN" + Environment.NewLine;
            code += CreateDeleteStatement(request) + Environment.NewLine;
            code += CreateFromStatement(request) + Environment.NewLine;
            code += CreateWhereStatement(request) + Environment.NewLine;
            code += "END" + Environment.NewLine;

            this.OutputFile.Write(code);
        }

        private string CreateDeleteStatement(Request request)
        {
            return "DELETE ";
        }

        private void DeleteExistingRequest(Request request)
        {
            string code="IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = '" + request.ReqName + "')" + Environment.NewLine;
            code += "BEGIN" + Environment.NewLine;
            code+= "DROP  Procedure " + Formatter.FormatRequestName(request) + Environment.NewLine;
            code += "END" + Environment.NewLine;
            code += "GO" + Environment.NewLine;

            this.OutputFile.Write(code);
        }

        private void CreateInsertProcedure(Request request)
        {
            DeleteExistingRequest(request);

            string code = CreateProcedureHeader(request) + Environment.NewLine;
            code += CreateParametersDeclaration(request) + Environment.NewLine;
            code += "BEGIN" + Environment.NewLine;
            code += CreateReturnValueDeclaration() + Environment.NewLine;
            code += CreateInsertIntoStatement(request) + Environment.NewLine;
            code += CreateInsertColumns(request) + Environment.NewLine;
            code += CreateInsertValues(request) + Environment.NewLine;
            code += CreateSelectNewIdStatement() + Environment.NewLine;
            code += "END" + Environment.NewLine;

            this.OutputFile.Write(code);
        }

        private string CreateInsertValues(Request request)
        {
            return string.Empty;
            throw new NotImplementedException();
        }

        private string CreateInsertColumns(Request request)
        {
            if (request == null || request.InsertColumnsCollection.Count.Equals(0))
                throw new ApplicationException("Cannot define insert columns");
            string code = string.Empty;
            foreach (InsertColumns ic in request.InsertColumnsCollection)
            {
                if (string.IsNullOrEmpty(code))
                    code += "(" + Environment.NewLine;
                else
                    code += "," + Environment.NewLine + "\t"; 
                code +=Formatter.FormatColumnName(ic.Column);
            }
            if (!string.IsNullOrEmpty(code))
                code += Environment.NewLine + ")";
            return code;
        }

        private string CreateSelectNewIdStatement()
        {
            return "SELECT @ID";
        }

        private string CreateReturnValueDeclaration()
        {
            string code = "DECLARE @ID uniqueidentifier;" + Environment.NewLine;
            code += "SET @ID=newid();" + Environment.NewLine;
            return code;
        }

        private string CreateInsertIntoStatement(Request request)
        {
            return "INSERT INTO " + Formatter.FormatTableName(request.Table);
        }

        private void CreateUpdateProcedure(Request request)
        {
            DeleteExistingRequest(request);
        }

        private void CreateSelectProcedure(Request request)
        {
            DeleteExistingRequest(request);

            string code = CreateProcedureHeader(request) + Environment.NewLine;
            code += CreateParametersDeclaration(request) + Environment.NewLine;
            code += "BEGIN" + Environment.NewLine;
            code += CreateSelectStatement(request) + Environment.NewLine;
            code += CreateFromStatement(request) + Environment.NewLine;
            code += CreateWhereStatement(request) + Environment.NewLine;
            code += "END" + Environment.NewLine;

            this.OutputFile.Write(code);
        }

        private string CreateParametersDeclaration(Request request)
        {
            if (request == null)
                return string.Empty;

            string code = string.Empty;
            string parameters = string.Empty;
            if (request.RequestParameterCollection.Count > 0)
            {
                code += "(" + Environment.NewLine;
                foreach (RequestParameter param in request.RequestParameterCollection)
                {
                    if (!string.IsNullOrEmpty(parameters))
                    {
                        parameters += " ," + Environment.NewLine;
                    }
                    parameters += "\t" + Formatter.FormatParameterName(param);
                }
                code += parameters + Environment.NewLine;
                code += ")" + Environment.NewLine;
            }
            return code;
        }

        private string CreateProcedureHeader(Request request)
        {
            if (request == null)
                return string.Empty;
            return "CREATE PROCEDURE " + Formatter.FormatRequestName(request);
        }

        private string CreateWhereStatement(Request request)
        {
            if (request.RequestParameterCollection.Count.Equals(0))
                return string.Empty;

            string code = "WHERE " + Environment.NewLine;
            string parametersCode = string.Empty;

            foreach (RequestParameter param in request.RequestParameterCollection)
            {
                if (!string.IsNullOrEmpty(parametersCode))
                {
                    parametersCode += " AND " + Environment.NewLine;
                }

                parametersCode += "\t" + this.Formatter.FormatColumnName(param.Column) + " = " + this.Formatter.FormatParameterName(param); 
            }
            code += parametersCode;

            return code;
        }

        private string CreateFromStatement(Request request)
        {
            if (request == null)
                return string.Empty;

            return "FROM " + Formatter.FormatTableName(request.Table);
        }

        private string CreateSelectStatement(Request request)
        {
            if (request == null)
                return string.Empty;

            string code = "SELECT " + Environment.NewLine;
            string columns = string.Empty;
            foreach (SelectColumns sc in request.SelectColumnsCollection)
            {
                if (!string.IsNullOrEmpty(columns))
                {
                    columns += ", " + Environment.NewLine;
                }
                columns += "\t" + Formatter.FormatColumnName(sc.Column);
            }
            code += columns;

            return code;
        }

        private void SetDatabase()
        {
            if (this.ParametersInstances.Count > 0 &&
                this.ParametersInstances["Database"] != null)
            {
                this.Database = Database.Load(this.ParametersInstances["Database"].ModParInsValue);
                if (this.Database == null)
                {
                    ModuleParameterInstance instance = ModuleParameterInstance.Load(this.ParametersInstances["Database"].ModParInsValue);
                    while (instance != null)
                    {
                        this.Database = Database.Load(instance.ModParInsValue);
                        if (this.Database == null)
                            instance = ModuleParameterInstance.Load(instance.ModParInsValue);
                        else
                            break;
                    }
                    if (this.Database == null)
                    {
                        throw new ApplicationException("Unable to load Database parameter");
                    }
                }
            }
        }

        public override bool Generate()
        {
            
            return true;
        }
    }
}
